--DO NOT MODIFY THIS FILE. IT IS ALWAYS OVERWRITTEN ON GENERATION.

--This SQL is generated for the model defined view [actor_info]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[actor_info]') and [xtype] = 'V')
drop view [dbo].[actor_info]
--MODELID: b646018d-331b-4201-8e31-59547e86ab4f
GO

CREATE VIEW [dbo].[actor_info]
AS

select `a`.`actor_id` AS `actor_id`,`a`.`first_name` AS `first_name`,`a`.`last_name` AS `last_name`,group_concat(distinct concat(`c`.`name`,': ',(select group_concat(`f`.`title` order by `f`.`title` ASC separator ', ') from ((`film` `f` join `film_category` `fc` on((`f`.`film_id` = `fc`.`film_id`))) join `film_actor` `fa` on((`f`.`film_id` = `fa`.`film_id`))) where ((`fc`.`category_id` = `c`.`category_id`) and (`fa`.`actor_id` = `a`.`actor_id`)))) order by `c`.`name` ASC separator '; ') AS `film_info` from (((`actor` `a` left join `film_actor` `fa` on((`a`.`actor_id` = `fa`.`actor_id`))) left join `film_category` `fc` on((`fa`.`film_id` = `fc`.`film_id`))) left join `category` `c` on((`fc`.`category_id` = `c`.`category_id`))) group by `a`.`actor_id`,`a`.`first_name`,`a`.`last_name`
--MODELID,BODY: b646018d-331b-4201-8e31-59547e86ab4f
GO
exec sp_refreshview N'[dbo].[actor_info]';
--MODELID: b646018d-331b-4201-8e31-59547e86ab4f
GO

--This SQL is generated for the model defined view [customer_list]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[customer_list]') and [xtype] = 'V')
drop view [dbo].[customer_list]
--MODELID: cb6c2048-bc65-4d76-9e16-954926ad554f
GO

CREATE VIEW [dbo].[customer_list]
AS

select `cu`.`customer_id` AS `ID`,concat(`cu`.`first_name`,_utf8' ',`cu`.`last_name`) AS `name`,`a`.`address` AS `address`,`a`.`postal_code` AS `zip code`,`a`.`phone` AS `phone`,`city`.`city` AS `city`,`country`.`country` AS `country`,if(`cu`.`active`,_utf8'active',_utf8'') AS `notes`,`cu`.`store_id` AS `SID` from (((`customer` `cu` join `address` `a` on((`cu`.`address_id` = `a`.`address_id`))) join `city` on((`a`.`city_id` = `city`.`city_id`))) join `country` on((`city`.`country_id` = `country`.`country_id`)))
--MODELID,BODY: cb6c2048-bc65-4d76-9e16-954926ad554f
GO
exec sp_refreshview N'[dbo].[customer_list]';
--MODELID: cb6c2048-bc65-4d76-9e16-954926ad554f
GO

--This SQL is generated for the model defined view [film_list]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[film_list]') and [xtype] = 'V')
drop view [dbo].[film_list]
--MODELID: b1016253-6d6e-4315-8065-2621ce0306d0
GO

CREATE VIEW [dbo].[film_list]
AS

select `film`.`film_id` AS `FID`,`film`.`title` AS `title`,`film`.`description` AS `description`,`category`.`name` AS `category`,`film`.`rental_rate` AS `price`,`film`.`length` AS `length`,`film`.`rating` AS `rating`,group_concat(concat(`actor`.`first_name`,_utf8' ',`actor`.`last_name`) separator ', ') AS `actors` from ((((`category` left join `film_category` on((`category`.`category_id` = `film_category`.`category_id`))) left join `film` on((`film_category`.`film_id` = `film`.`film_id`))) join `film_actor` on((`film`.`film_id` = `film_actor`.`film_id`))) join `actor` on((`film_actor`.`actor_id` = `actor`.`actor_id`))) group by `film`.`film_id`
--MODELID,BODY: b1016253-6d6e-4315-8065-2621ce0306d0
GO
exec sp_refreshview N'[dbo].[film_list]';
--MODELID: b1016253-6d6e-4315-8065-2621ce0306d0
GO

--This SQL is generated for the model defined view [nicer_but_slower_film_list]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[nicer_but_slower_film_list]') and [xtype] = 'V')
drop view [dbo].[nicer_but_slower_film_list]
--MODELID: bd7751b8-3d83-4985-9b8d-7b266e7670a9
GO

CREATE VIEW [dbo].[nicer_but_slower_film_list]
AS

select `film`.`film_id` AS `FID`,`film`.`title` AS `title`,`film`.`description` AS `description`,`category`.`name` AS `category`,`film`.`rental_rate` AS `price`,`film`.`length` AS `length`,`film`.`rating` AS `rating`,group_concat(concat(concat(ucase(substr(`actor`.`first_name`,1,1)),lcase(substr(`actor`.`first_name`,2,length(`actor`.`first_name`))),_utf8' ',concat(ucase(substr(`actor`.`last_name`,1,1)),lcase(substr(`actor`.`last_name`,2,length(`actor`.`last_name`)))))) separator ', ') AS `actors` from ((((`category` left join `film_category` on((`category`.`category_id` = `film_category`.`category_id`))) left join `film` on((`film_category`.`film_id` = `film`.`film_id`))) join `film_actor` on((`film`.`film_id` = `film_actor`.`film_id`))) join `actor` on((`film_actor`.`actor_id` = `actor`.`actor_id`))) group by `film`.`film_id`
--MODELID,BODY: bd7751b8-3d83-4985-9b8d-7b266e7670a9
GO
exec sp_refreshview N'[dbo].[nicer_but_slower_film_list]';
--MODELID: bd7751b8-3d83-4985-9b8d-7b266e7670a9
GO

--This SQL is generated for the model defined view [sales_by_film_category]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sales_by_film_category]') and [xtype] = 'V')
drop view [dbo].[sales_by_film_category]
--MODELID: 278f9aca-171e-4318-aee4-ef1157a10620
GO

CREATE VIEW [dbo].[sales_by_film_category]
AS

select `c`.`name` AS `category`,sum(`p`.`amount`) AS `total_sales` from (((((`payment` `p` join `rental` `r` on((`p`.`rental_id` = `r`.`rental_id`))) join `inventory` `i` on((`r`.`inventory_id` = `i`.`inventory_id`))) join `film` `f` on((`i`.`film_id` = `f`.`film_id`))) join `film_category` `fc` on((`f`.`film_id` = `fc`.`film_id`))) join `category` `c` on((`fc`.`category_id` = `c`.`category_id`))) group by `c`.`name` order by sum(`p`.`amount`) desc
--MODELID,BODY: 278f9aca-171e-4318-aee4-ef1157a10620
GO
exec sp_refreshview N'[dbo].[sales_by_film_category]';
--MODELID: 278f9aca-171e-4318-aee4-ef1157a10620
GO

--This SQL is generated for the model defined view [sales_by_store]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sales_by_store]') and [xtype] = 'V')
drop view [dbo].[sales_by_store]
--MODELID: 2f54bcc1-83bf-4ce6-92b2-58f624f221e4
GO

CREATE VIEW [dbo].[sales_by_store]
AS

select concat(`c`.`city`,_utf8',',`cy`.`country`) AS `store`,concat(`m`.`first_name`,_utf8' ',`m`.`last_name`) AS `manager`,sum(`p`.`amount`) AS `total_sales` from (((((((`payment` `p` join `rental` `r` on((`p`.`rental_id` = `r`.`rental_id`))) join `inventory` `i` on((`r`.`inventory_id` = `i`.`inventory_id`))) join `store` `s` on((`i`.`store_id` = `s`.`store_id`))) join `address` `a` on((`s`.`address_id` = `a`.`address_id`))) join `city` `c` on((`a`.`city_id` = `c`.`city_id`))) join `country` `cy` on((`c`.`country_id` = `cy`.`country_id`))) join `staff` `m` on((`s`.`manager_staff_id` = `m`.`staff_id`))) group by `s`.`store_id` order by `cy`.`country`,`c`.`city`
--MODELID,BODY: 2f54bcc1-83bf-4ce6-92b2-58f624f221e4
GO
exec sp_refreshview N'[dbo].[sales_by_store]';
--MODELID: 2f54bcc1-83bf-4ce6-92b2-58f624f221e4
GO

--This SQL is generated for the model defined view [staff_list]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[staff_list]') and [xtype] = 'V')
drop view [dbo].[staff_list]
--MODELID: 665b132e-6916-4781-b08b-b2cbff877d99
GO

CREATE VIEW [dbo].[staff_list]
AS

select `s`.`staff_id` AS `ID`,concat(`s`.`first_name`,_utf8' ',`s`.`last_name`) AS `name`,`a`.`address` AS `address`,`a`.`postal_code` AS `zip code`,`a`.`phone` AS `phone`,`city`.`city` AS `city`,`country`.`country` AS `country`,`s`.`store_id` AS `SID` from (((`staff` `s` join `address` `a` on((`s`.`address_id` = `a`.`address_id`))) join `city` on((`a`.`city_id` = `city`.`city_id`))) join `country` on((`city`.`country_id` = `country`.`country_id`)))
--MODELID,BODY: 665b132e-6916-4781-b08b-b2cbff877d99
GO
exec sp_refreshview N'[dbo].[staff_list]';
--MODELID: 665b132e-6916-4781-b08b-b2cbff877d99
GO

